#-*- coding: UTF-8 -*-
import MySQLdb
import MySQLdb.cursors
import traceback
import sys
import datetime
import time
import math
reload(sys)
sys.setdefaultencoding('utf-8')

conn= MySQLdb.connect(
        host='127.0.0.1',
        port = 3306,
        user='root',
        passwd='01827746521',
        db ='fof',
        cursorclass = MySQLdb.cursors.DictCursor,
        charset="utf8"
        )
cur = conn.cursor()


def getLatestFundIndicator():
    sql = '''
        SELECT
            a.ext_code,
            a.ext_source,
            a.swaNav,
            a.yieldAnnually,
            a.drawdown,
            a.volatilityAnnually,
            a.yield1y,
            a.sharpRatio,
            a.yield3y,
            a.yield5y,
            a.navDate

        FROM
            fof_fund_indicator a
        INNER JOIN (
            SELECT
                ext_code,
                max(navDate) navDate
            FROM
                fof_fund_indicator
            GROUP BY
                ext_code
        ) b ON a.ext_code = b.ext_code
        AND a.navDate = b.navDate
        ORDER BY
            a.ext_code;
    '''
    # sql = 'select * from fof_fund_indicator limit 10'
    cur.execute(sql)
    return cur.fetchall()


def getLatestFundNav():
    sql = '''
        SELECT
            a.fund_ext_code,
            a.fund_ext_source,
            a.nav,
            a.added_nav,
            a.swanav,
            a.statistic_date
        FROM
            fund_nav a
        INNER JOIN (
            SELECT
                fund_ext_code,
                max(statistic_date) statistic_date
            FROM
                fund_nav
            GROUP BY
                fund_ext_code
        ) b ON a.fund_ext_code = b.fund_ext_code
        AND a.statistic_date = b.statistic_date
        ORDER BY
            a.fund_ext_code;
    '''
    # sql = 'select * from fund_nav limit 10'
    cur.execute(sql)
    return cur.fetchall()

def getAllBenchmarkFof():
    sql = '''
        select windCode from fof_benchmark
    '''
    cur.execute(sql)
    return cur.fetchall()

def getYield(fundExtCode,year):
    todayNavSql = 'select date,eod_price from `benchmark_price` where `index_code` = "%s" order by date desc limit 1' % (fundExtCode)
    cur.execute(todayNavSql)
    result = cur.fetchone()
    endNav = result['eod_price']
    endDate = result['date']

    now = datetime.datetime.now()
    startDate = datetime.datetime((now.year-year),now.month,now.day)

    startNavSql = 'select date,eod_price from `benchmark_price` where `index_code` = "%s" and date <= "%s" order by date desc limit 1' % (fundExtCode,startDate.strftime('%Y-%m-%d'))
    print startNavSql
    cur.execute(startNavSql)
    result = cur.fetchone()
    startNav = result['eod_price']

    print endNav/startNav
    print 1.0/year
    return math.pow( endNav/startNav, 1.0/year ) - 1


allRows = getAllBenchmarkFof()
for row in allRows:
    fundExtCode = row['windCode']
    yield1y = getYield(fundExtCode,1)
    yield3y = getYield(fundExtCode,3)
    yield5y = getYield(fundExtCode,5)

    sql = '''
        INSERT INTO fund_info(fund_ext_code,fund_ext_source,yield1y,yield3y,yield5y) VALUES
        ("%s",1,%.2f,%.2f,%.2f) ON DUPLICATE KEY UPDATE yield1y = VALUES(yield1y),
        yield3y = VALUES(yield3y),
        yield5y = VALUES(yield5y)
    '''%(fundExtCode,yield1y,yield3y,yield5y )

    print sql
    cur.execute(sql)

conn.commit()
print 'full benchmark fof end'

allRows = getLatestFundNav();
for row in allRows:
    try:
        # sql = '''
        #     update fund_info SET unitNav = %(nav)s,
        #      accumulateNav = %(added_nav)s,
        #      lastUpdateDate = '%(statistic_date)s'
        #     WHERE fund_ext_code = '%(fund_ext_code)s'
        # '''%row

        if row['nav'] is None:
            row['nav'] = 0
        if row['added_nav'] is None:
            row['added_nav'] = 0
        if row['swanav'] is None:
            row['swanav'] = 0

        sql = '''
          INSERT INTO fund_info(fund_ext_code,fund_ext_source,unitNav,accumulateNav,swanav,statisticDate) VALUES
                                ('%(fund_ext_code)s',%(fund_ext_source)s,%(nav)s,%(added_nav)s,%(swanav)s,'%(statistic_date)s')
                                ON DUPLICATE KEY UPDATE fund_ext_code = VALUES(fund_ext_code),
                                fund_ext_source = VALUES(fund_ext_source),
                                unitNav = VALUES(unitNav),
                                accumulateNav = VALUES(accumulateNav),
                                swanav = VALUES(swanav),
                                statisticDate = VALUES(statisticDate);
        '''%row
        print sql
        cur.execute(sql)
    except Exception as e:
        print traceback.print_exc()
        continue

conn.commit()


allRows = getLatestFundIndicator();
for row in allRows:
    try:

        # sql = '''
        #     update fund_info SET unitNav = %(swaNav)s,
        #      yieldAnnually = %(yieldAnnually)s,
        #      drawdown = %(drawdown)s,
        #      volatilityAnnually = %(volatilityAnnually)s,
        #      yield1y = %(yield1y)s,
        #      sharpRatio = %(sharpRatio)s,
        #      accumulateNav = %(swaNav)s,
        #      yield3y = %(yield3y)s,
        #      yield5y = %(yield5y)s
        #     WHERE fund_ext_code = '%(ext_code)s'
        # '''%row

        if row['swaNav'] is None:
            row['swaNav'] = 0

        if row['yield5y'] is None:
            row['yield5y'] = 0

        if row['swaNav'] is None:
            row['swaNav'] = 0


        sql = '''
            INSERT INTO fund_info(fund_ext_code,fund_ext_source,unitNav, yieldAnnually, drawdown,volatilityAnnually,yield1y,sharpRatio,accumulateNav,yield3y,yield5y,lastUpdateDate) VALUES
                                  ('%(ext_code)s',%(ext_source)s,%(swaNav)s,%(yieldAnnually)s,%(drawdown)s,%(volatilityAnnually)s,%(yield1y)s,%(sharpRatio)s,%(swaNav)s,%(yield3y)s,%(yield5y)s,'%(navDate)s')
                                  ON DUPLICATE KEY UPDATE fund_ext_code = VALUES(fund_ext_code),
                                  fund_ext_source = VALUES(fund_ext_source),
                                  unitNav = VALUES(unitNav),
                                  yieldAnnually = VALUES(yieldAnnually),
                                  drawdown = VALUES(drawdown),
                                  volatilityAnnually = VALUES(volatilityAnnually),
                                  yield1y = VALUES(yield1y),
                                  sharpRatio = VALUES(sharpRatio),
                                  accumulateNav = VALUES(accumulateNav),
                                  yield3y = VALUES(yield3y),
                                  lastUpdateDate = VALUES(lastUpdateDate),
                                  yield5y = VALUES(yield5y);
        '''%row
        print sql
        cur.execute(sql)
    except Exception as e:
        print traceback.print_exc()
        continue

cur.execute("update fund_info set `fund_status` = 1 where fund_ext_code like '%FOF'")

conn.commit()



cur.close()
conn.close()
